package gen.tools.withdraw;

import gen.tools.Widgets;
import gen.tools.structures.InfoStruc;
import gen.tools.structures.S2;
import gen.tools.structures.S5;

import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import com.google.gson.Gson;

public class QueryDeliverAble extends HttpServlet {
	private static final long serialVersionUID = 1L;
       
    public QueryDeliverAble() {
        super();
    }

	protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		doPost(request,response);
	}

	protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		final Widgets wid = new Widgets();
		request.setCharacterEncoding( wid.UIcodeSet );
		response.setContentType("application/json");
		response.setCharacterEncoding( wid.UIcodeSet );
		
		Connection conn = null;
		Connection tarConn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		ResultSet rs2 = null;
		final DepCube dc = new DepCube();
		final ArrayList<ReadyDeploy> gfs = new ArrayList<ReadyDeploy>();
		final ArrayList<ReadyDeploy> rds = new ArrayList<ReadyDeploy>();
		final ArrayList<InfoStruc> eas = new ArrayList<InfoStruc>();
		final int statusSN =	Integer.parseInt(request.getParameter("statusSN") );
		final int targetSystemId =	Integer.parseInt(request.getParameter("targetSystemId") );	//提交對象的系統的身份
		try {
			conn = wid.getConn();
			String strSQL = "SELECT t3.comReqsNum,t1.deliveryComment,t1.deplmainSN,t3.isnonweb,t3.depldetailid,t5.reqId,t5.reqDesc,t3.localFile,t3.depPath, t3.localVer,t6.empName,t6.empPhone FROM deltask t1,deplmain t2,depldetail t3,mpreqfiles t4,reqs t5,emps t6 WHERE t1.deliverySN=? AND t1.deplmainSN=t2.deplmainSN AND t2.deplmainSN=t3.deplmainSN AND t3.deplmainSN=t4.deplmainSN AND t3.depldetailid=t4.depldetailid AND t4.reqSN=t5.reqId AND t2.creator=t6.empId ORDER BY t3.depldetailid";

			/**
			 * 因為這支 QueryDeliverAble，在提領和部署時皆有用到
			 * 部署時可以查自己的，但提領時，就只能查對方的了
			 * */
			if ( statusSN == 2 ) {	//	狀態是待提領，所以要連去對方的DB
				tarConn = wid.getTarConnection(request, targetSystemId);		//取得接收方的資料庫連結
				ps = tarConn.prepareStatement( strSQL );
			} else {
				ps = conn.prepareStatement( strSQL );	
			}
														
			ps.setString(1, request.getParameter("deliverSN_q") );
			rs = ps.executeQuery();
			
			int idx = 0;	//用來處理部署細項中的，共用程式 
			ArrayList<S2> rqs = null;
			
			while ( rs.next() ) {
				
				ReadyDeploy rd = new ReadyDeploy();
				if ( rs.getInt("comReqsNum") != 0 &&  idx == 0  ) {
					rqs = new ArrayList<S2>();
				}
				
				if ( rs.getInt("comReqsNum") == 0 ||   (idx+1)  ==  rs.getInt("comReqsNum")   ) {
					rd.setDepType("t".equalsIgnoreCase( rs.getString("isnonweb") )?"non-web":"web");
					rd.setComReqsNum( rs.getInt("comReqsNum") );
					rd.setDeplmainSN( rs.getInt("deplmainSN") );
					rd.setDepldetailid( rs.getInt("depldetailid") );
					rd.setLocalFile( rs.getString("localFile") );
					rd.setLocalVer( rs.getInt("localVer") );
					rd.setCreatorName( rs.getString("empName") );
					rd.setCreatorPhone( rs.getString("empPhone") );
					rd.setComment( rs.getString("deliveryComment") );
					rd.setDepPath( rs.getString("depPath") );
					rd.setStatusSN(statusSN);
					
					if ( rs.getInt("comReqsNum") != 0 ) {
						S2 rq = new S2();
						rq.setS1(  String.valueOf( rs.getInt("reqId") ) );
						rq.setS2( rs.getString("reqDesc") );
						rqs.add(rq);
					} else {
						rd.setReqSN( rs.getInt("reqId") );
						rd.setReqID( rs.getString("reqDesc") );	
					}
				} else {
					if ( rs.getInt("comReqsNum") != 0 ) {
						S2 rq = new S2();
						rq.setS1(  String.valueOf( rs.getInt("reqId") ) );
						rq.setS2(  rs.getString("reqDesc") );
						rqs.add(rq);
					}
				}
				
				/*	該項要部署的子系統&資訊群組	*/ 
				final ArrayList<S5> sqs = new ArrayList<S5>();
				
				/*	為避免多次存取DB，利用此次提交亦查詢可點選的	*/
				strSQL = "SELECT t1.subsysId,t3.subsysDesc,t1.squadid, t2.squadname,t2.squadpath FROM mpdeplunitsqu t1,squad t2, subsyses t3 WHERE deplmainSN=? AND depldetailid=? AND t1.subsysId=t2.subsysId AND t1.squadid=t2.squadid AND t3.subsysId=t1.subsysId";
				if (statusSN == 2) {	//	狀態是待提領，所以要連去對方的DB
					ps = tarConn.prepareStatement(strSQL);
				} else {
					ps = conn.prepareStatement(strSQL);	
				}
				ps.setString(1, rs.getString("deplmainSN") );
				ps.setInt(2, rd.getDepldetailid() );
				rs2 = ps.executeQuery();
				while ( rs2.next() ) {
					
					/*	先將此支部署計畫細項的資訊群組寫入	*/ 
					S5 sq = new S5();
					sq.setS1( rs2.getString("subsysId") );			//子系統ID
					sq.setS2( rs2.getString("squadid") );				//資源群組ID
					sq.setS3( rs2.getString("squadpath") );		//部署路徑
					sq.setS4( rs2.getString("subsysDesc")  );	//子系統名稱
					sq.setS5( rs2.getString("squadname") );		//資源群組名稱
					sqs.add(sq);
					
					/*	先將【不同的】 子系統-資訊群組  組合都記錄下來	*/
					boolean isNotDuplicated = true; 
					for(int i=0;i<eas.size();i++){
						if ( rs2.getString("subsysId").equalsIgnoreCase(eas.get(i).getId()) && rs2.getString("squadid").equalsIgnoreCase(eas.get(i).getName() ) ) {
							isNotDuplicated = false;	//為false 表示已存在
							break;
						}
					}
					if (isNotDuplicated) {
						InfoStruc ea = new InfoStruc();
						ea.setId( rs2.getString("subsysId") );
						ea.setName( rs2.getString("squadid") );
						eas.add(ea);	
					}
				}
				rd.setSqs(sqs);
				
				if ( rs.getInt("comReqsNum") ==0 ) {	//表示此筆部署細項是 「需求下轄程式」
					rds.add(rd);	
				} else if ( rs.getInt("comReqsNum") != 0 && (idx+1) == rs.getInt("comReqsNum") ) {
					rd.setRqs(rqs);
					gfs.add(rd);
					idx=0;
				} else {
					idx++;
				}
			} 
			dc.setGfs(gfs);		//共用程式清單
			dc.setRds(rds);		//需求下轄程式清單
			dc.setSqs(eas);
			
		} catch ( Exception e) {
			wid.errLog(e);
		}  finally {
			try {
				if (rs != null) {rs.close();}
				if (rs2 != null) {rs2.close();}
				if (ps!= null) {ps.close();}
				if (conn!= null) {conn.close();}
				if (tarConn!= null) {tarConn.close();}
			} catch (SQLException e) {
				System.out.println(wid.errProcess(e) );
			}
		}
		
		response.getWriter().write(new Gson().toJson(dc) );
	
	}

}
